﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.IO;
using System.Data;
using System.Threading;
using NPOI.HSSF.Util;
using System.Data.Common;
using System.Reflection;
using System.Web;

namespace UpFile
{

    /// <summary>
    /// 特殊样式表的数据对象
    /// </summary>
    public class cellValues
    {
        public cellValues()
        {
            this.DataType = TypeCode.String;
        }
        public int Row { get; set; }
        public int Col { get; set; }
        public TypeCode DataType { get; set; }
        public string Valuer { get; set; }
        public string FormatValue { get; set; }
    }
    public static class ExportFileClass
    {
        /// <summary>
        /// 特殊样式表的导出
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="lstCell"></param>
        /// <returns></returns>
        public static MemoryStream ListExport(string filePath, List<cellValues> lstCell)
        {

            string fullPath = HttpContext.Current.Server.MapPath(filePath);


            HSSFWorkbook hssfworkbook;
            using (FileStream file = new FileStream(fullPath, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            ISheet sheet1 = hssfworkbook.GetSheetAt(0);

            System.Collections.IEnumerator rows = sheet1.GetRowEnumerator();
            if (lstCell.Count() > 0)
            {
                foreach (var item in lstCell)
                {
                    if (item.Row == 44)
                    {
                    }
                    IRow row = sheet1.GetRow(item.Row);
                    ICell cell = row.GetCell(item.Col);
                    switch (item.DataType)
                    {
                        case TypeCode.Boolean:
                            cell.SetCellType(CellType.BOOLEAN);
                            cell.SetCellValue(item.Valuer);
                            break;

                        case TypeCode.Object:
                            cell.SetCellType(CellType.Unknown);
                            cell.SetCellValue(item.Valuer);
                            break;
                        case TypeCode.Int16:
                        case TypeCode.Int32:
                        case TypeCode.Int64:
                        case TypeCode.SByte:
                        case TypeCode.Single:
                        case TypeCode.UInt16:
                        case TypeCode.UInt32:
                        case TypeCode.UInt64:
                        case TypeCode.Decimal:
                        case TypeCode.Double:
                            if (item.Valuer != null)
                            {
                                cell.SetCellType(CellType.NUMERIC);
                                cell.SetCellValue(double.Parse(item.Valuer));
                            }
                            else
                            {
                                cell.SetCellType(CellType.FORMULA);
                                cell.SetCellFormula(item.FormatValue);
                            }
                            break;
                        case TypeCode.String:
                        case TypeCode.DateTime:
                        case TypeCode.Byte:
                        case TypeCode.Char:
                        case TypeCode.DBNull:
                        case TypeCode.Empty:
                            cell.SetCellType(CellType.STRING);
                            cell.SetCellValue(item.Valuer);
                            break;
                        default:
                            cell.SetCellType(CellType.STRING);
                            cell.SetCellValue(item.Valuer);
                            break;
                    }


                }
            }
            MemoryStream mstream = new MemoryStream();
            hssfworkbook.Write(mstream);
            return mstream;
        }


        public static Stream ListExport2Stream<T>(List<T> list, string head, string nameEn)
        {

            if (!string.IsNullOrEmpty(head))
            {
                System.Reflection.PropertyInfo[] ps = typeof(T).GetProperties();
                List<PropertyInfo> lstProperty = new List<PropertyInfo>();
                string[] strEn = nameEn.Split('|');
                foreach (var item in strEn)
                {
                    var p = ps.FirstOrDefault(t => t.Name.ToLower() == item.ToLower());
                    if (p != null)
                    {
                        lstProperty.Add(p);
                    }
                }

                string[] strs = head.Split('|');

                HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
                ISheet sheet = hssfWorkbook.CreateSheet("sheet1");
                IRow row = sheet.CreateRow(0);
                ICell cell = row.CreateCell(strs.Length);
                ICellStyle style = hssfWorkbook.CreateCellStyle();
                IFont font = hssfWorkbook.CreateFont();
                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 12;
                font.Boldweight = 600;
                style.SetFont(font);
                IRow row_one = sheet.CreateRow(0);
                ICell cells = null;
                for (int i = 0; i < lstProperty.Count; i++)
                {
                    //有几个列标题就创建几个Excel的单元格
                    cells = row_one.CreateCell(i);
                    //获取每个列标题填充给对应的单元格
                    cells.SetCellValue(strs[i] + "\t");
                    cells.CellStyle = style;

                    sheet.SetColumnWidth(i, 25 * 256);
                    int colWidth3 = sheet.GetColumnWidth(i);
                }
                IRow totalRow = null;
                for (int i = 1; i <= list.Count(); i++)
                {
                    totalRow = sheet.CreateRow(i);
                    for (int j = 1; j <= lstProperty.Count; j++)
                    {
                        object o = lstProperty[j - 1].GetValue(list[i - 1], null);
                        cells = totalRow.CreateCell(j - 1);
                        cells.SetCellValue(o + "\t");

                        sheet.SetColumnWidth(j - 1, 25 * 150);
                        int colWidth3 = sheet.GetColumnWidth(j - 1);
                    }
                }
                MemoryStream mstream = new MemoryStream();
                hssfWorkbook.Write(mstream);
                return mstream;

            }
            return null;
        }
        /// <summary>
        /// 导出一个DataTable中的数据到Excel中
        /// </summary>
        /// <param name="table">DataTable对象</param>
        /// <param name="fileName">导出后的文件名称</param>
        /// <param name="filePath">导出文件的存储目录</param>
        public static void DataTableExport(DataTable table, string fileName, string filePath)
        {

            HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
            ISheet sheet = hssfWorkbook.CreateSheet("sheet1");
            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell(table.Columns.Count);
            //ICellStyle cellStyle = hssfWorkbook.CreateCellStyle();
            //cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            //IFont font = hssfWorkbook.CreateFont();
            //font.FontHeight = 20 * 20;
            //cellStyle.SetFont(font);
            //cell.CellStyle = cellStyle;
            //cell.SetCellValue(fileName);
            //sheet.AddMergedRegion(new CellRangeAddress(0, 0, 5, 11));
            IRow row_one = sheet.CreateRow(0);
            ICell cells = null;
            for (int i = 0; i < table.Columns.Count; i++)
            {
                //有几个列标题就创建几个Excel的单元格
                cells = row_one.CreateCell(i);
                //获取每个列标题填充给对应的单元格
                cells.SetCellValue(table.Columns[i].ColumnName + "\t");
            }
            IRow totalRow = null;
            for (int i = 1; i <= table.Rows.Count; i++)
            {
                totalRow = sheet.CreateRow(i);
                for (int j = 1; j <= table.Columns.Count; j++)
                {
                    cells = totalRow.CreateCell(j - 1);
                    cells.SetCellValue(table.Rows[i - 1][j - 1].ToString() + "\t");
                }
            }
            FileStream file = new FileStream(filePath, FileMode.Create);
            hssfWorkbook.Write(file);
            file.Close();
        }
        /// <summary>
        /// 导出多个DataTable中的数据到Excel中
        /// </summary>
        /// <param name="list">DataTable的List集合</param>
        /// <param name="filePath">导出文件的存储目录</param>
        public static void DataTableExport(List<DataTable> list, string filePath)
        {
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
            ISheet sheet = null;
            for (int i = 0; i < list.Count; i++)
            {
                switch (i)
                {
                    case 0:
                        sheet = hssfWorkbook.CreateSheet("任务");
                        break;
                    case 1:
                        sheet = hssfWorkbook.CreateSheet("标记");
                        break;
                    case 2:
                        sheet = hssfWorkbook.CreateSheet("时段");
                        break;
                    default:
                        break;
                }
                IRow row_one = sheet.CreateRow(0);
                ICell cells = null;
                DataTable table = list[i];
                if (table != null)
                {
                    IFont font = hssfWorkbook.CreateFont();
                    font.FontName = "宋体";
                    font.Boldweight = (short)FontBoldWeight.BOLD;
                    ICellStyle cellStyle = hssfWorkbook.CreateCellStyle();
                    //cellStyle.FillBackgroundColor = HSSFColor.YELLOW.index;
                    //cellStyle.FillPattern = FillPatternType.NO_FILL;
                    //cellStyle.FillForegroundColor = HSSFColor.YELLOW.index;
                    cellStyle.SetFont(font);
                    for (int k = 0; k < table.Columns.Count; k++)
                    {
                        //有几个列标题就创建几个Excel的单元格
                        cells = row_one.CreateCell(k);
                        cells.CellStyle = cellStyle;
                        //获取每个列标题填充给对应的单元格
                        cells.SetCellValue(table.Columns[k].ColumnName + "\t");
                    }
                    if (table.Rows.Count > 0)
                    {
                        IRow totalRow = null;
                        for (int m = 1; m <= table.Rows.Count; m++)
                        {
                            totalRow = sheet.CreateRow(m);
                            for (int j = 1; j <= table.Columns.Count; j++)
                            {
                                cells = totalRow.CreateCell(j - 1);
                                cells.SetCellValue(table.Rows[m - 1][j - 1].ToString() + "\t");
                            }
                        }
                    }
                }
                //switch (i)
                //{
                //    case 0:
                //        export.SetProgressValue(30);
                //        break;
                //    case 1:
                //        export.SetProgressValue(60);
                //        break;
                //    case 2:
                //        export.SetProgressValue(100);
                //        break;
                //    default:
                //        break;
                //}
            }
            FileStream file = new FileStream(filePath, FileMode.Create);
            hssfWorkbook.Write(file);
            file.Close();
        }

    }
}
